USE master
GO

--create ssb database
CREATE DATABASE SSB
GO
--You need to mark database TRUSTWORTHY so that it can 
--access resource beyond the scope of this database
ALTER DATABASE SSB SET TRUSTWORTHY ON
GO

USE SSB
GO

--create master key 
--SELECT * FROM sys.symmetric_keys
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ssbrouter';
GO

/**create dialog certificate**/
CREATE USER SSBRouterDbo WITHOUT LOGIN;
GO

--Creating certificate which will be used for encrypting messages.
--SELECT * FROM sys.certificates; 
CREATE CERTIFICATE DlgCert_SSBRouter
       AUTHORIZATION SSBRouterDbo
       WITH SUBJECT = N'Certificate For SSBRouter',
       START_DATE = N'1/1/2012',
       EXPIRY_DATE = N'1/1/2022';

--The certificate needs to backed up and restored at the other communicating 
--partner so that the messages can be decrypted by the receiver of the message. 
--The location which you specify for backing up/restoring certificate must be 
--accessible by the account under which database engine is running.
BACKUP CERTIFICATE DlgCert_SSBRouter
       TO FILE = N'D:\SSBCertificates\DlgCert_SSBRouter.cer';
GO

--web001 database's certificate
CREATE USER Web001Dbo WITHOUT LOGIN

SELECT * FROM SSB.SYS.certificates;

CREATE CERTIFICATE DlgCert_Web001
	AUTHORIZATION Web001Dbo
    FROM FILE = 'D:\SSBCertificates\DlgCert_Web001.cer' 
GO

--WMS DB's certificate
CREATE USER WMS001Dbo WITHOUT LOGIN

CREATE CERTIFICATE DlgCert_WMS001
	AUTHORIZATION WMS001Dbo
    FROM FILE = 'D:\SSBCertificates\DlgCert_WMS001.cer' 
GO

--WMS DB2's certificate
CREATE USER WMS002Dbo WITHOUT LOGIN

CREATE CERTIFICATE DlgCert_WMS002
	AUTHORIZATION WMS002Dbo
    FROM FILE = 'D:\SSBCertificates\DlgCert_WMS002.cer' 
GO


